﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Web.Utility;
using Portal.Utility;
using Portal.Core;
using Portal.Exam.Model;


namespace Portal.Exam.Bussiness
{
    public class ExamRoomStudentBll
    {
        string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public ExamRoomStudentCls CreateModel(string SiteId, string ExamRoomStudentId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = " select TableExamRoomStudent.*,ExamTemplateId,ExamTemplateType,ExamTitle=IsNull(ExamTitle,ExamSubject),ExamDescription=IsNull(ExamDescription,''), " +
                           "     TotalNeedReview=(select count(ExamQuestionId) from TableExamQuestion where MarkedNeedReview=1 and frkExamRoomStudentId=ExamRoomStudentId), " +
                           "     TotalDone=(select count(ExamQuestionId) from TableExamQuestion where frkExamRoomStudentId=ExamRoomStudentId and not StudentAnswer is null and StudentAnswer<>'') " +
                           "  from TableExamRoomStudent " +
                           "     join TableExamRoom on TableExamRoom.ExamRoomId=frkExamRoomId" +
                           "     where ExamRoomStudentId = '" + ExamRoomStudentId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return null;
            }

            ExamRoomStudentCls tExamRoomStudentCls = new ExamRoomStudentCls();
            tExamRoomStudentCls.ExamRoomStudentId = (string)dtResult.Rows[0]["ExamRoomStudentId"];
            tExamRoomStudentCls.ExamRoomId = (string)dtResult.Rows[0]["frkExamRoomId"];
            tExamRoomStudentCls.StudentCode = (string)dtResult.Rows[0]["StudentCode"];
            tExamRoomStudentCls.StudentName = (string)dtResult.Rows[0]["StudentName"];
            tExamRoomStudentCls.Sex = (int)dtResult.Rows[0]["Sex"];
            if (dtResult.Rows[0]["Dob"] != DBNull.Value)
            {
                tExamRoomStudentCls.Dob = (DateTime)dtResult.Rows[0]["Dob"];
            }

            if (dtResult.Rows[0]["ExamTemplateId"] != DBNull.Value)
            {
                tExamRoomStudentCls.ExamTemplateId = (string)dtResult.Rows[0]["ExamTemplateId"];
            }

            if (dtResult.Rows[0]["ExamTitle"] != DBNull.Value)
            {
                tExamRoomStudentCls.ExamTitle = (string)dtResult.Rows[0]["ExamTitle"];
            }

            if (dtResult.Rows[0]["ExamDescription"] != DBNull.Value)
            {
                tExamRoomStudentCls.ExamDescription = (string)dtResult.Rows[0]["ExamDescription"];
            }


            tExamRoomStudentCls.Course = (string)dtResult.Rows[0]["Course"];
            tExamRoomStudentCls.Class = (string)dtResult.Rows[0]["Class"];
            tExamRoomStudentCls.TrainingLevel = (string)dtResult.Rows[0]["TrainingLevel"];
            tExamRoomStudentCls.TotalPoint = (int)dtResult.Rows[0]["TotalPoint"];
            tExamRoomStudentCls.Passpoint = (int)dtResult.Rows[0]["Passpoint"];
            if (dtResult.Rows[0]["ExamDate"] != DBNull.Value)
            {
                tExamRoomStudentCls.ExamDate = (DateTime)dtResult.Rows[0]["ExamDate"];
            }
            if (dtResult.Rows[0]["StartExam"] != DBNull.Value)
            {
                tExamRoomStudentCls.StartExam = (DateTime)dtResult.Rows[0]["StartExam"];
            }
            tExamRoomStudentCls.FinishExam = (int)dtResult.Rows[0]["FinishExam"];
            tExamRoomStudentCls.UseTime = (int)dtResult.Rows[0]["UseTime"];
            tExamRoomStudentCls.HasGenQuestion = (int)dtResult.Rows[0]["HasGenQuestion"];
            tExamRoomStudentCls.DonePoint = (int)dtResult.Rows[0]["DonePoint"];
            if (dtResult.Rows[0]["TotalQuestion"] != DBNull.Value)
            {
                tExamRoomStudentCls.TotalQuestion = (int)dtResult.Rows[0]["TotalQuestion"];
            }

            tExamRoomStudentCls.TotalDone = (int)dtResult.Rows[0]["TotalDone"];
            tExamRoomStudentCls.TotalNeedReview = (int)dtResult.Rows[0]["TotalNeedReview"];

            if (dtResult.Rows[0]["TotalExamTime"] != DBNull.Value)
            {
                tExamRoomStudentCls.TotalExamTime = (int)dtResult.Rows[0]["TotalExamTime"];
            }

            if (dtResult.Rows[0]["ExamTemplateType"] != DBNull.Value)
            {
                tExamRoomStudentCls.ExamTemplateType = dtResult.Rows[0]["ExamTemplateType"].ToString();
            }
            else
            {
                tExamRoomStudentCls.ExamTemplateType = "";
            }


            return tExamRoomStudentCls;
        }

        public DataTable ReadingExamRoomStudentByStudentCode(
           string SiteId,
           string ExamRoomStudentId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dataTable = new DataTable();
            string Query = "select * from TableExamRoomStudent where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and ExamRoomStudentId='" + ExamRoomStudentId + "'";

            Query += " order by StudentCode";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamRoomStudent(
            string SiteId,
            string ExamRoomId,
            string Keyword)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dataTable = new DataTable();
            string Query = "select * from TableExamRoomStudent where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and frkExamRoomId='" + ExamRoomId + "'";

            if (Keyword == null) Keyword = "";
            else
            {
                Keyword = FunctionUtilities.CorrectForSearch(Keyword);
                Query += " and StudentCode like N'%" + Keyword + "%'";
            }
            Query += " order by StudentCode";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamRoomStudentForSearch(
           string SiteId,
           string ExamRoomId,
           string Keyword,
           string ExamDate)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dataTable = new DataTable();
            string Query = //"select * from TableExamRoomStudent where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and frkExamRoomId='" + ExamRoomId + "'";

                "  SELECT * " +
                "  FROM [ExamHpmu].[dbo].[TableExamRoomStudent] student,TableExamRoom room " +
                "  where student.frkExamRoomId = room.ExamRoomId " +
                "  and  student.frkExamRoomId = '" + ExamRoomId + "' ";
            //"  and student.StudentCode like  N'%1%' " +
            //"  and room.StartTime = '2013-06-01 00:00:00.000' \n";

            if (Keyword == null) Keyword = "";
            else
            {
                Keyword = FunctionUtilities.CorrectForSearch(Keyword);
                Query += " and student.StudentCode like N'%" + Keyword + "%'";
            }

            if (!string.IsNullOrEmpty(ExamDate))
            {
                DateTime OExamDate = FunctionUtilities.VNDateToDate(ExamDate);
                Query += "  and room.StartTime = '" + OExamDate + "' \n";
            }

            Query += " order by StudentCode";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingQuestionTypeExamTemp(
          string SiteId,
          string ExamTemplateId,
          string Keyword)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select Category from TableExamTemplate where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and ExamTemplateId='" + ExamTemplateId + "'";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingExamRoomStudentByExamRoomNo(
            string SiteId,
            string ExamSegmentId,
            string ExamRoomNo)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dataTable = new DataTable();
            ExamRoomNo = FunctionUtilities.CorrectForSearch(ExamRoomNo);
            string Query = "select TableExamRoomStudent.* from TableExamRoomStudent " +
                           "        join TableExamRoom on TableExamRoom.ExamRoomId=frkExamRoomId" +
                           "     where TableExamRoom.AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' " +
                           "        and RoomNo='" + ExamRoomNo + "' " +
                           "        and frkExamSegmentId='" + ExamSegmentId + "' " +
                           "     order by StudentCode";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamRoomStudentHasFinished(
           string SiteId,
           string ExamRoomId,
           string Keyword)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select * from TableExamRoomStudent where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and frkExamRoomId='" + ExamRoomId + "' and FinishExam=1 order by StudentCode";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public void CreateExamRoomStudent(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomId,
            string StudentCode,
            string StudentName,
            int Sex,
            DateTime Dob,
            string Course,
            string Class,
            string TrainingLevel,
            string AssetCode            )
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewExamRoomStudentId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableExamRoomStudent(ExamRoomStudentId, frkExamRoomId, StudentCode, StudentName, Sex, Dob, Course, Class, TrainingLevel, TotalPoint, Passpoint, ExamDate, FinishExam, UseTime, HasGenQuestion, DonePoint, AssetCode, TotalQuestion, TotalExamTime, Mark) values (@ExamRoomStudentId, @frkExamRoomId, @StudentCode, @StudentName, @Sex, @Dob, @Course, @Class, @TrainingLevel, 0, 0, null, 0, 0, 0, 0, @AssetCode,0,0,0)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamRoomStudentId"].Value = NewExamRoomStudentId;

            TempSqlCommand.Parameters.Add("@frkExamRoomId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkExamRoomId"].Value = ExamRoomId;

            TempSqlCommand.Parameters.Add("@StudentCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@StudentCode"].Value = StudentCode;

            TempSqlCommand.Parameters.Add("@StudentName", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@StudentName"].Value = StudentName;

            TempSqlCommand.Parameters.Add("@Sex", SqlDbType.Int);
            TempSqlCommand.Parameters["@Sex"].Value = Sex;

            TempSqlCommand.Parameters.Add("@Dob", SqlDbType.DateTime);
            if (Dob == new System.DateTime())
            {
                TempSqlCommand.Parameters["@Dob"].Value = DBNull.Value;
            }
            else
            {
                TempSqlCommand.Parameters["@Dob"].Value = Dob;
            }

            TempSqlCommand.Parameters.Add("@Course", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Course"].Value = Course;

            TempSqlCommand.Parameters.Add("@Class", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Class"].Value = Class;

            TempSqlCommand.Parameters.Add("@TrainingLevel", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@TrainingLevel"].Value = TrainingLevel;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = AssetCode;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                if (ex.Message.ToString().IndexOf("IX_TableExamRoomStudent") != -1)
                {
                    throw new Exception("Đã có mã sinh viên " + StudentCode);
                }
                else
                {
                    throw (ex);
                }
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void UpdateExamRoomStudent(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomStudentId,
            string StudentCode,
            string StudentName,
            int Sex,
            DateTime Dob,
            string Course,
            string Class,
            string TrainingLevel)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamRoomStudent set StudentCode=@StudentCode, StudentName=@StudentName, Sex=@Sex,Dob=@Dob,Course=@Course,Class=@Class,TrainingLevel=@TrainingLevel where ExamRoomStudentId=@ExamRoomStudentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamRoomStudentId"].Value = ExamRoomStudentId;

            TempSqlCommand.Parameters.Add("@StudentCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@StudentCode"].Value = StudentCode;

            TempSqlCommand.Parameters.Add("@StudentName", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@StudentName"].Value = StudentName;

            TempSqlCommand.Parameters.Add("@Sex", SqlDbType.Int);
            TempSqlCommand.Parameters["@Sex"].Value = Sex;

            TempSqlCommand.Parameters.Add("@Dob", SqlDbType.DateTime);
            TempSqlCommand.Parameters["@Dob"].Value = Dob;

            TempSqlCommand.Parameters.Add("@Course", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Course"].Value = Course;

            TempSqlCommand.Parameters.Add("@Class", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Class"].Value = Class;

            TempSqlCommand.Parameters.Add("@TrainingLevel", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@TrainingLevel"].Value = TrainingLevel;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void DeleteExamRoomStudent(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomStudentId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Delete TableExamRoomStudent where ExamRoomStudentId=@ExamRoomStudentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamRoomStudentId"].Value = ExamRoomStudentId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void MakeExam(
            string SiteId,
            string ExamTemplateId,
            string ExamRoomId,
            bool ReCreateSession)
        {
            ExamTemplateCls OExamTemplate = new ExamTemplateBll().CreateModel(SiteId, ExamTemplateId);
            DataTable dtExamListStudent = ReadingExamRoomStudent(SiteId, ExamRoomId, null);
            SqlConnection OSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            OSqlConnection.Open();
            SqlTransaction OSqlTransaction = OSqlConnection.BeginTransaction();
            try
            {
                for (int iIndex = 0; iIndex < dtExamListStudent.Rows.Count; iIndex++)
                {
                    string ExamRoomStudentId = (string)dtExamListStudent.Rows[iIndex]["ExamRoomStudentId"];
                    MakeExam(OSqlTransaction, SiteId, ExamTemplateId, ExamRoomStudentId, OExamTemplate.ExamTime, ReCreateSession);
                }
                OSqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                OSqlTransaction.Rollback();
                throw (ex);
            }
        }

        public DataSet GetQuestionsByExamTemplateId(
          string SiteId,
          string ExamTemplateId)
        {
            string Query = "";

            DataTable dtExamConfig = new ExamTemplateBll().ReadingItems(SiteId, ExamTemplateId);
            if (dtExamConfig.Rows.Count == 0) throw new Exception("Chưa thiết lập chi tiết cho template đề thi");
            for (int iIndex = 0; iIndex < dtExamConfig.Rows.Count; iIndex++)
            {
                string ExamCollectionId = (string)dtExamConfig.Rows[iIndex]["ExamCollectionId"];
                string QuestionType = (string)dtExamConfig.Rows[iIndex]["QuestionType"];
                int NoOfQuestion = (int)dtExamConfig.Rows[iIndex]["NoOfQuestion"];
                int LevelQuestion = (int)dtExamConfig.Rows[iIndex]["LevelQuestion"];
                Query += "SELECT TOP " + NoOfQuestion + " TableQuestion.* FROM TableExamCollectionQuestion " +
                         "      join TableQuestion on TableQuestion.QuestionId=frkExamQuestionId" +
                         "  where frkExamCollectionId='" + ExamCollectionId + "' and [Level]=" + LevelQuestion + " and [Type]='" + QuestionType + "' ORDER By NEWID();\r\n";
            }

            DataSet dsQuestion = new DataSet();
            SqlConnection TempSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            TempSqlConnection.Open();
            SqlCommand tSqlCommand = TempSqlConnection.CreateCommand();
            tSqlCommand.CommandText = Query;
            SqlDataAdapter sqlda = new SqlDataAdapter(tSqlCommand);
            sqlda.Fill(dsQuestion);

            return dsQuestion;
        }

        public void MakeExamByExamTemplate(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId,
            string ExamRoomStudentId,
            int TotalExamTime,
            bool ReCreateSession,
            string NoOfQuestion,
            string TemplateConfig)
        {
            string Query = "";
            ExamRoomStudentCls OExamRoomStudent = new ExamRoomStudentBll().CreateModel(SiteId, ExamRoomStudentId);
            if (OExamRoomStudent.FinishExam == 1 && ReCreateSession == false)
            {
                return;
            }
            //DataTable dtExamConfig = new ExamTemplateBll().ReadingItems(SiteId, ExamTemplateId);
            //if (dtExamConfig.Rows.Count == 0) throw new Exception("Chưa thiết lập chi tiết cho template đề thi");
            //for (int iIndex = 0; iIndex < dtExamConfig.Rows.Count; iIndex++)
            //{
            //    string ExamCollectionId = (string)dtExamConfig.Rows[iIndex]["ExamCollectionId"];
            //    string QuestionType = (string)dtExamConfig.Rows[iIndex]["QuestionType"];
            //    //int NoOfQuestion = (int)dtExamConfig.Rows[iIndex]["NoOfQuestion"];
            //    int LevelQuestion = (int)dtExamConfig.Rows[iIndex]["LevelQuestion"];
            //    Query += "SELECT TOP " + NoOfQuestion + " TableQuestion.* FROM TableExamCollectionQuestion " +
            //             "      join TableQuestion on TableQuestion.QuestionId=frkExamQuestionId" +
            //             "  where frkExamCollectionId='" + ExamCollectionId + "' and [Level]=" + LevelQuestion + " and [Type]='" + QuestionType + "' ORDER By NEWID();\r\n";
            //}


            //DataTable dtExamConfig = new ExamTemplateBll().ReadingItems(SiteId, ExamTemplateId);

            if (TemplateConfig.Trim().Length > 0)
            {
                string[] dtExamConfig = TemplateConfig.Split(';');

                if (dtExamConfig.Length == 0) throw new Exception("Chưa thiết lập chi tiết cho template đề thi");
                for (int iIndex = 0; iIndex < dtExamConfig.Length; iIndex++)
                {
                    int LevelQuestion = int.Parse(dtExamConfig[iIndex].Split(':')[0].ToString());
                    int NoQuestion = (int.Parse(dtExamConfig[iIndex].Split(':')[1].ToString()) * int.Parse(NoOfQuestion)) / 100;

                    if (NoQuestion == 0)
                        continue;

                    Query += "SELECT TOP " + NoQuestion + " TableQuestion.* FROM TableExamTemplateQuestion " +
                             "      join TableQuestion on TableQuestion.QuestionId=frkQuestionId" +
                             "  where frkTemplateId='" + ExamTemplateId + "' and [Level]=" + LevelQuestion + " ORDER By NEWID();\r\n";
                }
            }
            else
            {
                Query += "SELECT TableQuestion.* FROM TableExamTemplateQuestion " +
                             "      join TableQuestion on TableQuestion.QuestionId=frkQuestionId" +
                             "  where frkTemplateId='" + ExamTemplateId + "' ORDER By NEWID();\r\n";
            }

            DataSet dsQuestion = new DataSet();
            SqlConnection TempSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            TempSqlConnection.Open();
            SqlCommand tSqlCommand = TempSqlConnection.CreateCommand();
            tSqlCommand.CommandText = Query;
            SqlDataAdapter sqlda = new SqlDataAdapter(tSqlCommand);
            sqlda.Fill(dsQuestion);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            //int TotalExamTime = 0;
            int TotalQuestion = 0;
            int TotalPoint = 0;
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;

            TempSqlCommand.CommandText =
                " Delete from TableExamAnswer where frkExamQuestionId in (select ExamQuestionId from TableExamQuestion where frkExamRoomStudentId='" + ExamRoomStudentId + "'); " +
                " Delete from TableExamQuestion where frkExamRoomStudentId='" + ExamRoomStudentId + "'";
            TempSqlCommand.Parameters.Clear();
            TempSqlCommand.ExecuteNonQuery();

            //Query = "Insert into TableExamQuestion(ExamQuestionId,frkExamRoomStudentId,Title,Question,Type,MaxPoint,CorrectAnswer,StudentAnswer,MarkedNeedReview,SecondTime,QuestionIndex,AnswerTip,IsMemory,SecondHidden,ExamQuestionLevel,ExamEmbbedHtml, TeacherFlashAnswer) values (@ExamQuestionId,@frkExamRoomStudentId,@Title,@Question,@Type,@MaxPoint,@CorrectAnswer,@StudentAnswer,@MarkedNeedReview,@SecondTime,@QuestionIndex,@AnswerTip,@IsMemory,@SecondHidden,@ExamQuestionLevel,@ExamEmbbedHtml, @TeacherFlashAnswer);";

            //Query += "Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,@CorrectAnswer_ExamAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
            ////"Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
            //TempSqlCommand.CommandText = Query;


            try
            {
                int QuestionIndex = 0;
                for (int iIndex = 0; iIndex < dsQuestion.Tables.Count; iIndex++)
                {
                    for (int iRow = 0; iRow < dsQuestion.Tables[iIndex].Rows.Count; iRow++)
                    {
                        string ExamQuestionId = System.Guid.NewGuid().ToString();
                        string QuestionId = (string)dsQuestion.Tables[iIndex].Rows[iRow]["QuestionId"];
                        string Title = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Title"];
                        string Question = "";
                        try
                        {
                            Question = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Question"];
                        }
                        catch { }
                        string Type = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Type"];
                        string AnswerTip = "";
                        try
                        {
                            AnswerTip = (string)dsQuestion.Tables[iIndex].Rows[iRow]["AnswerTip"];
                        }
                        catch
                        {
                        }
                        int Level = (int)dsQuestion.Tables[iIndex].Rows[iRow]["Level"];
                        int MaxPoint = (int)dsQuestion.Tables[iIndex].Rows[iRow]["MaxPoint"];
                        TotalPoint = TotalPoint + MaxPoint;
                        TotalQuestion = TotalQuestion + 1;
                        //int CorrectAnswer = (int)dsQuestion.Tables[iIndex].Rows[iRow]["CorrectOrder"];
                        int CorrectAnswer = 0; ;
                        int SecondTime = 15;
                        try
                        {
                            if (dsQuestion.Tables[iIndex].Rows[iRow]["SecondTime"] != DBNull.Value)
                            {
                                SecondTime = (int)dsQuestion.Tables[iIndex].Rows[iRow]["SecondTime"];
                            }
                        }
                        catch { }
                        int IsMemory = 0;
                        try
                        {
                            IsMemory = (int)dsQuestion.Tables[iIndex].Rows[iRow]["IsMemory"];
                        }
                        catch { }

                        int SecondHidden = 0;
                        try
                        {
                            SecondHidden = (int)dsQuestion.Tables[iIndex].Rows[iRow]["SecondHidden"];
                        }
                        catch { }

                        byte[] EmbbedHtml = null;
                        if (dsQuestion.Tables[iIndex].Rows[iRow]["EmbbedHtml"] != DBNull.Value)
                        {
                            EmbbedHtml = (byte[])dsQuestion.Tables[iIndex].Rows[iRow]["EmbbedHtml"];
                        }

                        string CorrectAnswerFlash = "";
                        if (dsQuestion.Tables[iIndex].Rows[iRow]["CorrectAnswerFlash"] != DBNull.Value)
                        {
                            CorrectAnswerFlash = (string)dsQuestion.Tables[iIndex].Rows[iRow]["CorrectAnswerFlash"];
                        }

                        bool isQuestImage = false;
                        if (Type.ToLower().Equals("image"))
                        {
                            isQuestImage = true;
                        }

                        Query = "Insert into TableExamQuestion(ExamQuestionId,frkExamRoomStudentId,Title,Question,Type,MaxPoint,CorrectAnswer,StudentAnswer,MarkedNeedReview,SecondTime,QuestionIndex,AnswerTip,IsMemory,SecondHidden,ExamQuestionLevel,ExamEmbbedHtml, TeacherFlashAnswer) values (@ExamQuestionId,@frkExamRoomStudentId,@Title,@Question,@Type,@MaxPoint,@CorrectAnswer,@StudentAnswer,@MarkedNeedReview,@SecondTime,@QuestionIndex,@AnswerTip,@IsMemory,@SecondHidden,@ExamQuestionLevel,@ExamEmbbedHtml, @TeacherFlashAnswer);";

                        if (isQuestImage)
                        {
                            Query += "Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer,ImageFileName)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,@CorrectAnswer_ExamAnswer,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
                        }
                        else
                        {
                            Query += "Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
                        }
                        //"Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
                        TempSqlCommand.CommandText = Query;

                        TempSqlCommand.Parameters.Clear();
                        TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

                        TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

                        TempSqlCommand.Parameters.Add("@frkExamRoomStudentId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@frkExamRoomStudentId"].Value = ExamRoomStudentId;

                        TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@Title"].Value = Title;

                        TempSqlCommand.Parameters.Add("@Question", SqlDbType.NText);
                        TempSqlCommand.Parameters["@Question"].Value = Question;

                        TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@Type"].Value = Type;

                        TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
                        TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

                        TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.Int);
                        TempSqlCommand.Parameters["@CorrectAnswer"].Value = CorrectAnswer;

                        if (isQuestImage)
                        {
                            TempSqlCommand.Parameters.Add("@CorrectAnswer_ExamAnswer", SqlDbType.VarChar);
                            TempSqlCommand.Parameters["@CorrectAnswer_ExamAnswer"].Value = QuestionId;
                        }

                        TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@StudentAnswer"].Value = "";

                        TempSqlCommand.Parameters.Add("@AnswerTip", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@AnswerTip"].Value = AnswerTip;


                        TempSqlCommand.Parameters.Add("@MarkedNeedReview", SqlDbType.Int);
                        TempSqlCommand.Parameters["@MarkedNeedReview"].Value = 0;

                        TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
                        TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

                        TempSqlCommand.Parameters.Add("@QuestionIndex", SqlDbType.Int);
                        TempSqlCommand.Parameters["@QuestionIndex"].Value = QuestionIndex++;

                        TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
                        TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory;

                        TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
                        TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

                        TempSqlCommand.Parameters.Add("@ExamQuestionLevel", SqlDbType.Int);
                        TempSqlCommand.Parameters["@ExamQuestionLevel"].Value = Level;

                        TempSqlCommand.Parameters.Add("@ExamEmbbedHtml", SqlDbType.Image);
                        if (EmbbedHtml != null)
                        {
                            TempSqlCommand.Parameters["@ExamEmbbedHtml"].Value = EmbbedHtml;
                        }
                        else
                        {
                            TempSqlCommand.Parameters["@ExamEmbbedHtml"].Value = DBNull.Value;
                        }

                        TempSqlCommand.Parameters.Add("@TeacherFlashAnswer", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@TeacherFlashAnswer"].Value = CorrectAnswerFlash.Trim();

                        TempSqlCommand.ExecuteNonQuery();
                    }
                }

                TempSqlCommand.CommandText = "Update TableExamRoomStudent set FinishExam=0,UseTime=0,StartExam=null, TotalPoint=" + TotalPoint + ",TotalQuestion=" + TotalQuestion + ",DonePoint=0,Mark=0, HasGenQuestion=1,TotalExamTime=" + TotalExamTime + " where ExamRoomStudentId='" + ExamRoomStudentId + "'";
                TempSqlCommand.Parameters.Clear();
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void MakeExam(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId,
            string ExamRoomStudentId,
            int TotalExamTime,
            bool ReCreateSession)
        {
            string Query = "";
            ExamRoomStudentCls OExamRoomStudent = new ExamRoomStudentBll().CreateModel(SiteId, ExamRoomStudentId);
            if (OExamRoomStudent.FinishExam == 1 && ReCreateSession == false)
            {
                return;
            }
            DataTable dtExamConfig = new ExamTemplateBll().ReadingItems(SiteId, ExamTemplateId);
            if (dtExamConfig.Rows.Count == 0) throw new Exception("Chưa thiết lập chi tiết cho template đề thi");
            for (int iIndex = 0; iIndex < dtExamConfig.Rows.Count; iIndex++)
            {
                string ExamCollectionId = (string)dtExamConfig.Rows[iIndex]["ExamCollectionId"];
                string QuestionType = (string)dtExamConfig.Rows[iIndex]["QuestionType"];
                int NoOfQuestion = (int)dtExamConfig.Rows[iIndex]["NoOfQuestion"];
                int LevelQuestion = (int)dtExamConfig.Rows[iIndex]["LevelQuestion"];
                Query += "SELECT TOP " + NoOfQuestion + " TableQuestion.* FROM TableExamCollectionQuestion " +
                         "      join TableQuestion on TableQuestion.QuestionId=frkExamQuestionId" +
                         "  where frkExamCollectionId='" + ExamCollectionId + "' and [Level]=" + LevelQuestion + " and [Type]='" + QuestionType + "' ORDER By NEWID();\r\n";
            }

            DataSet dsQuestion = new DataSet();
            SqlConnection TempSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            TempSqlConnection.Open();
            SqlCommand tSqlCommand = TempSqlConnection.CreateCommand();
            tSqlCommand.CommandText = Query;
            SqlDataAdapter sqlda = new SqlDataAdapter(tSqlCommand);
            sqlda.Fill(dsQuestion);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }



            //int TotalExamTime = 0;
            int TotalQuestion = 0;
            int TotalPoint = 0;
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;

            TempSqlCommand.CommandText =
                " Delete from TableExamAnswer where frkExamQuestionId in (select ExamQuestionId from TableExamQuestion where frkExamRoomStudentId='" + ExamRoomStudentId + "'); " +
                " Delete from TableExamQuestion where frkExamRoomStudentId='" + ExamRoomStudentId + "'";
            TempSqlCommand.Parameters.Clear();
            TempSqlCommand.ExecuteNonQuery();

            //Query = "Insert into TableExamQuestion(ExamQuestionId,frkExamRoomStudentId,Title,Question,Type,MaxPoint,CorrectAnswer,StudentAnswer,MarkedNeedReview,SecondTime,QuestionIndex,AnswerTip,IsMemory,SecondHidden,ExamQuestionLevel,ExamEmbbedHtml, TeacherFlashAnswer) values (@ExamQuestionId,@frkExamRoomStudentId,@Title,@Question,@Type,@MaxPoint,@CorrectAnswer,@StudentAnswer,@MarkedNeedReview,@SecondTime,@QuestionIndex,@AnswerTip,@IsMemory,@SecondHidden,@ExamQuestionLevel,@ExamEmbbedHtml, @TeacherFlashAnswer);";

            //Query += "Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,@CorrectAnswer_ExamAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
            ////"Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
            //TempSqlCommand.CommandText = Query;

            try
            {
                int QuestionIndex = 0;
                for (int iIndex = 0; iIndex < dsQuestion.Tables.Count; iIndex++)
                {
                    for (int iRow = 0; iRow < dsQuestion.Tables[iIndex].Rows.Count; iRow++)
                    {
                        string ExamQuestionId = System.Guid.NewGuid().ToString();
                        string QuestionId = (string)dsQuestion.Tables[iIndex].Rows[iRow]["QuestionId"];
                        string Title = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Title"];
                        string Question = "";
                        try
                        {
                            Question = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Question"];
                        }
                        catch { }
                        string Type = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Type"];
                        string AnswerTip = "";
                        try
                        {
                            AnswerTip = (string)dsQuestion.Tables[iIndex].Rows[iRow]["AnswerTip"];
                        }
                        catch
                        {
                        }
                        int Level = (int)dsQuestion.Tables[iIndex].Rows[iRow]["Level"];
                        int MaxPoint = (int)dsQuestion.Tables[iIndex].Rows[iRow]["MaxPoint"];
                        TotalPoint = TotalPoint + MaxPoint;
                        TotalQuestion = TotalQuestion + 1;
                        int CorrectAnswer = (int)dsQuestion.Tables[iIndex].Rows[iRow]["CorrectOrder"];
                        int SecondTime = 15;
                        try
                        {
                            if (dsQuestion.Tables[iIndex].Rows[iRow]["SecondTime"] != DBNull.Value)
                            {
                                SecondTime = (int)dsQuestion.Tables[iIndex].Rows[iRow]["SecondTime"];
                            }
                        }
                        catch { }
                        int IsMemory = 0;
                        try
                        {
                            IsMemory = (int)dsQuestion.Tables[iIndex].Rows[iRow]["IsMemory"];
                        }
                        catch { }

                        int SecondHidden = 0;
                        try
                        {
                            SecondHidden = (int)dsQuestion.Tables[iIndex].Rows[iRow]["SecondHidden"];
                        }
                        catch { }

                        byte[] EmbbedHtml = null;
                        if (dsQuestion.Tables[iIndex].Rows[iRow]["EmbbedHtml"] != DBNull.Value)
                        {
                            EmbbedHtml = (byte[])dsQuestion.Tables[iIndex].Rows[iRow]["EmbbedHtml"];
                        }

                        string CorrectAnswerFlash = "";
                        if (dsQuestion.Tables[iIndex].Rows[iRow]["CorrectAnswerFlash"] != DBNull.Value)
                        {
                            CorrectAnswerFlash = (string)dsQuestion.Tables[iIndex].Rows[iRow]["CorrectAnswerFlash"];
                        }

                        bool isQuestImage = false;
                        if (Type.ToLower().Equals("image"))
                        {
                            isQuestImage = true;
                        }

                        Query = "Insert into TableExamQuestion(ExamQuestionId,frkExamRoomStudentId,Title,Question,Type,MaxPoint,CorrectAnswer,StudentAnswer,MarkedNeedReview,SecondTime,QuestionIndex,AnswerTip,IsMemory,SecondHidden,ExamQuestionLevel,ExamEmbbedHtml, TeacherFlashAnswer) values (@ExamQuestionId,@frkExamRoomStudentId,@Title,@Question,@Type,@MaxPoint,@CorrectAnswer,@StudentAnswer,@MarkedNeedReview,@SecondTime,@QuestionIndex,@AnswerTip,@IsMemory,@SecondHidden,@ExamQuestionLevel,@ExamEmbbedHtml, @TeacherFlashAnswer);";
                        if (isQuestImage)
                        {
                            Query += "Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer,ImageFileName)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,@CorrectAnswer_ExamAnswer,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
                        }
                        else
                        {
                            Query += "Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
                        }
                        //"Insert into TableExamAnswer(ExamAnswerId,frkExamQuestionId, AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select newid(),@ExamQuestionId,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
                        TempSqlCommand.CommandText = Query;

                        TempSqlCommand.Parameters.Clear();
                        TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

                        TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

                        TempSqlCommand.Parameters.Add("@frkExamRoomStudentId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@frkExamRoomStudentId"].Value = ExamRoomStudentId;

                        TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@Title"].Value = Title;



                        TempSqlCommand.Parameters.Add("@Question", SqlDbType.NText);
                        TempSqlCommand.Parameters["@Question"].Value = Question;

                        TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@Type"].Value = Type;

                        TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
                        TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

                        TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.Int);
                        TempSqlCommand.Parameters["@CorrectAnswer"].Value = CorrectAnswer;

                        if (isQuestImage)
                        {
                            TempSqlCommand.Parameters.Add("@CorrectAnswer_ExamAnswer", SqlDbType.VarChar);
                            TempSqlCommand.Parameters["@CorrectAnswer_ExamAnswer"].Value = QuestionId;
                        }

                        TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@StudentAnswer"].Value = "";

                        TempSqlCommand.Parameters.Add("@AnswerTip", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@AnswerTip"].Value = AnswerTip;


                        TempSqlCommand.Parameters.Add("@MarkedNeedReview", SqlDbType.Int);
                        TempSqlCommand.Parameters["@MarkedNeedReview"].Value = 0;

                        TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
                        TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

                        TempSqlCommand.Parameters.Add("@QuestionIndex", SqlDbType.Int);
                        TempSqlCommand.Parameters["@QuestionIndex"].Value = QuestionIndex++;

                        TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
                        TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory;

                        TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
                        TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

                        TempSqlCommand.Parameters.Add("@ExamQuestionLevel", SqlDbType.Int);
                        TempSqlCommand.Parameters["@ExamQuestionLevel"].Value = Level;

                        TempSqlCommand.Parameters.Add("@ExamEmbbedHtml", SqlDbType.Image);
                        if (EmbbedHtml != null)
                        {
                            TempSqlCommand.Parameters["@ExamEmbbedHtml"].Value = EmbbedHtml;
                        }
                        else
                        {
                            TempSqlCommand.Parameters["@ExamEmbbedHtml"].Value = DBNull.Value;
                        }

                        TempSqlCommand.Parameters.Add("@TeacherFlashAnswer", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@TeacherFlashAnswer"].Value = CorrectAnswerFlash.Trim();

                        TempSqlCommand.ExecuteNonQuery();

                        //TotalExamTime = TotalExamTime + SecondTime;
                    }
                }

                TempSqlCommand.CommandText = "Update TableExamRoomStudent set FinishExam=0,UseTime=0,StartExam=null, TotalPoint=" + TotalPoint + ",TotalQuestion=" + TotalQuestion + ",DonePoint=0, HasGenQuestion=1,TotalExamTime=" + TotalExamTime + " where ExamRoomStudentId='" + ExamRoomStudentId + "'";
                TempSqlCommand.Parameters.Clear();
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void ResetExamRoomStudent(
            string SiteId,
            string ExamRoomStudentId)
        {
            SqlConnection OSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            OSqlConnection.Open();
            SqlTransaction OSqlTransaction = OSqlConnection.BeginTransaction();
            try
            {
                SqlCommand oSqlCommand = OSqlConnection.CreateCommand();
                oSqlCommand.Transaction = OSqlTransaction;
                oSqlCommand.CommandText = " Update TableExamRoomStudent set FinishExam=0, StartExam=null, UseTime=0 where ExamRoomStudentId=@ExamRoomStudentId";
                oSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.VarChar).Value = ExamRoomStudentId;
                oSqlCommand.ExecuteNonQuery();
                OSqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                OSqlTransaction.Rollback();
                throw (ex);
            }
        }
    }
}
